<?php
declare(strict_types = 1);
namespace Tool;
use Swoft\Core\ResultInterface;
use Swoft\Db\Exception\DbException;
use Swoft\Db\Query\Expression;
use Swoft\Stdlib\Helper\Arr;
use Tool\Query\EBuilder;


/**
 * Query 代理
 * @author wq
 */
class QueryBuilderProxy
{
    /**
     * 实例
     * @var QueryBuilderProxy
     */
    private static $instance;

    /**
     * @var EBuilder
     */
    private $queryBuild;


    /**
     *
     * @var array
     */
    private $field = [];

    /**
     * @return QueryBuilder
     */
    public function getQueryBuild()
    {
        return $this->queryBuild;
    }

    private function __construct(string $tableName)
    {
//         CollectionParserTool::init();

        $this->queryBuild = EBuilder::new()->from($tableName);

    }



    /**
     * @return Query
     */
    public static function getInstance(string $tableName) : self
    {
        return new static($tableName);
    }

    /**
     * @param mixed $column
     * @param int   $amount
     *
     * @return \Swoft\Core\ResultInterface
     */
    public function counter($column): int
    {
        return $this->queryBuild->count($column);
    }
    /**
     * where语句
     * @param string $column
     * @param mixed  $value
     * @param string $operator
     * @param string $connector
     * @return QueryBuilder
     */
    public function where(string $column, $value, $operator = '=', $connector = 'and'): QueryBuilderProxy
    {
        $this->queryBuild->where($column, $operator, $value, $connector);

        return $this;
    }

    /**
     * 获取执行的sql
     * @return string
     * @throws \ReflectionException
     * @throws \Swoft\Bean\Exception\ContainerException
     */
    public function getLastSql(): string
    {
        return $this->queryBuild->toSql();
    }

    /**
     * limit语句
     *
     * @param int $limit
     * @param int $offset
     *
     * @return QueryBuilder
     */
    public function limit(int $limit, $offset = 0) :self
    {
        $this->queryBuild->forPage($offset, $limit);

        return $this;
    }

    /**
     * 字段
     */
    public function field($field, bool $isTicket = false) :self
    {
        $this->field= is_string($field) ? explode(',', $field) : $field;

        return $this;
    }


    public function order(string $column, string $order = 'ASC') :self
    {
        $this->queryBuild->orderBy($column, $order);

        return $this;
    }

    /**
     * where条件中，括号开始(左括号)
     *
     * @param string $connector
     *
     * @return QueryBuilder
     */
    public function openWhere($connector = 'AND'): self
    {
        $this->queryBuild->openWhere($connector);

        return $this;
    }

    /**
     * where条件中，括号结束(右括号)
     *
     * @return QueryBuilder
     */
    public function closeWhere(): self
    {
        $this->queryBuild->closeWhere();

        return $this;
    }


    /**
     * 查询结果(一条数据)
     */
    public function find() :array
    {
        $this->parseColumn();

        $data = $this->queryBuild->first($this->field);

        if (!is_array($data)) {
            $data = (array)$data;
        }

        $this->field = [];

        return $data;
    }

    /**
     * 处理字段
     * @return array
     */
    private function parseColumn(): void
    {
        if (0 === count($this->field)) {
            $this->field = ['*'];
        } else {
            foreach ($this->field as &$value) {
                $value = Expression::new($value);
            }
        }
    }


    /**
     * 查询结果
     * @return array
     */
    public function select() :array
    {
        $this->parseColumn();

        $data = $this->queryBuild->addSelect($this->field)->get()->toArray();

        $this->field = [];

        return $data;
    }

    /**
     * 清理
     */
    public function clear() :void
    {
        $this->queryBuild = null;
    }

    /**
     * inner join语句
     *
     * @param string $table
     * @param string|array $criteria
     * @param string|null $operator
     * @param string|null $second
     * @return QueryBuilderProxy
     */
    public function innerJoin(string $table, $criteria = null, string $operator = null, string $second = null): self
    {
        $this->queryBuild->join($table, $criteria, $operator, $second);

        return $this;
    }

    public function selectRaw(string $expression, array $bindings = []): self
    {
        $this->queryBuild->selectRaw($expression, $bindings);

        return $this;
    }
    
   	/**
   	 * 运行条件更新语句
   	 * @param string $query
   	 * @return int
   	 */
    public function executeUpdateQuery(string $query): int
    {
    	return $this->queryBuild->getConnection()->update($query);
    }

    /**
     * left join语句
     *
     * @param string $table
     * @param string|array $criteria
     * @param string|null $operator
     * @param string|null $second
     * @return QueryBuilderProxy
     */
    public function leftJoin(string $table, $criteria = null, string $operator = null, string $second = null): self
    {
        $this->queryBuild->leftJoin($table, $criteria, $operator, $second);

        return $this;
    }

    /**
     * where and 语句
     *
     * @param string $column
     * @param mixed  $value
     * @param string $operator
     *
     * @return QueryBuilder
     */
    public function andWhere(string $column, $value, $operator = '='): self
    {
        $this->queryBuild->where($column, $operator, $value);

        return $this;
    }


    /**
     * where or 语句
     *
     * @param string $column
     * @param mixed  $value
     * @param string $operator
     *
     * @return QueryBuilder
     */
    public function orWhere($column, $value, $operator = '='): self
    {
        $this->queryBuild->orWhere($column, $operator, $value);

        return $this;
    }
    /**
     * group by语句
     *
     * @param string $column
     * @param string $order
     *
     * @return QueryBuilder
     */
    public function groupBy(string $column): self
    {
        $this->queryBuild->groupBy($column);

        return $this;
    }

    /**
     * having语句
     *
     * @param string $column
     * @param mixed  $value
     * @param string $operator
     * @param string $connector
     *
     * @return QueryBuilder
     */
    public function having(string $column, $value, string $operator = '=', string $connector = 'and'): self
    {
        $this->queryBuild->having($column, $operator, $value, $connector);

        return $this;
    }

    public function innerJoinSub(
        $query,
        string $as,
        $first,
        string $operator = null,
        string $second = null
    ): self {
        $this->queryBuild->joinSub($query, $as, $first, $operator, $second);

        return $this;
    }


    /**
     * where in 语句
     *
     * @param string $column
     * @param array  $values
     * @param string $connector
     *
     * @return QueryBuilder
     */
    public function whereIn(string $column, array $values, string $connector = 'AND'): self
    {

        $this->queryBuild->whereIn($column, $values, $connector);

        return $this;
    }


    /**
     * between语句
     * @param string $column
     * @param mixed  $min
     * @param mixed  $max
     * @param string $connector
     *
     * @return QueryBuilder
     */
    public function whereBetween(string $column, $min, $max, string $connector = 'AND'): self
    {
        $this->queryBuild->whereBetween($column, [$min, $max], $connector);

        return $this;
    }

    /**
     * The $condition is array
     *
     * Format `['column1' => value1, 'column2' => value2, ...]`
     * - ['name' => 'swoft', 'status' => 1] => ('name'='swoft' and 'status' = 1)
     * - ['id' => [1, 2, 3], 'status' => 1] => ('id' in (1, 2, 3) and 'status' = 1)
     *
     * Format `[operator, operand1, operand2, ...]`
     * - ['id', '>', 12]
     * - ['id', '<', 13]
     * - ['id', '>=', 13]
     * - ['id', '<=', 13]
     * - ['id', '<>', 13]
     *
     * - ['id', 'in', [1, 2, 3]]
     * - ['id', 'not in', [1, 2, 3]]
     *
     * - ['id', 'between', 2, 3]
     * - ['id', 'not between', 2, 3]
     *
     * - ['name', 'like', '%swoft%']
     * - ['name', 'not like', '%swoft%']
     *
     *
     * @param array $condition
     *
     * @return \Swoft\Db\QueryBuilder
     */
    public function condition(array $condition): self
    {
        $this->queryBuild->where($condition);

        return $this;
    }

    /**
     * @param array $condition
     */
    public function andCondition(array $condition) :self
    {
        $this->queryBuild->where($condition);

        return $this;
    }

    /**
     * 处理字段对应关系
     * @return array
     */
    public function getField() :array
    {
        $data = $this->queryBuild->get($this->field)->toArray();

        if (count($data) === 0) {
            return [];
        }

        $keyTwo = $this->field[1];


        $keyOne = $this->field[0];

        return $this->parseAssocData($data, $keyOne, $keyTwo);
    }

    /**
     * 处理字段对应关系(带有别名)
     * @return array
     */
    public function getAliasField() :array
    {
        $data = $this->queryBuild->get($this->field)->toArray();
        if (count($data) ===0) {
            $this->field = [];
            return [];
        }

        $field = array_keys($data[0]);

        $keyTwo = $field[1];

        $keyOne = $field[0];

        return $this->parseAssocData($data, $keyOne, $keyTwo);
    }

    /**
     *
     * @param array $data
     * @param string $keyTwo
     * @return array
     */
    private function parseAssocData(array $data, string $keyOne, string $keyTwo): array
    {
        $cols = [];

        $count = count($this->field);

        foreach ($data as $result){
            $name   =  $result[$keyOne];
            if(2 === $count) {
                $cols[$name]   =  $result[$keyTwo];
            } else {
                $cols[$name]   = $result;
            }
        }
        $this->field = [];

        return $cols;
    }

    /**
     * @param array $values
     *
     * @return ResultInterface
     */
    public function save(array $values): int
    {
        return $this->queryBuild->update($values);
    }

    /**
     * @param array $values
     *
     * @return ResultInterface
     * @throws MysqlException
     */
    public function add(array $values): int
    {
        return (int)$this->queryBuild->insertGetId($values);
    }

    /**
     * @param array $rows
     * @return ResultInterface
     * @throws MysqlException
     */
    public function addAll(array $values): int
    {
//        return  (int)$this->queryBuild->insert($rows);

        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient when building these
        // inserts statements by verifying these elements are actually an array.
        if (0 === count($values)) {
            return 0;
        }

        if (!is_array(reset($values))) {
            $values = [$values];
        }

        // Here, we will sort the insert keys for every record so that each insert is
        // in the same order for the record. We need to make sure this is the case
        // so there are not any errors or problems when inserting these records.
        else {
            foreach ($values as $key => $value) {
                ksort($value);

                $values[$key] = $value;
            }
        }

        // Finally, we will run this query against the database connection and return
        // the results. We will need to also flatten these bindings before running
        // the query so they are all in one huge, flattened array for execution.
        return (int)$this->queryBuild->getConnection()->insertGetIdStatement(
            $this->queryBuild->grammar->compileInsert($this->queryBuild, $values),
            $this->cleanBindings(Arr::flatten($values, 1))
        );
    }

    /**
     * Remove all of the expressions from a list of bindings.
     *
     * @param array $bindings
     *
     * @return array
     */
    protected function cleanBindings(array $bindings)
    {
        return array_values(array_filter($bindings, function ($binding) {
            return !$binding instanceof Expression;
        }));
    }

    /**
     * delete语句
     */
    public function delete() :int
    {
        return $this->queryBuild->delete();
    }

    /**
     * @param string $column
     * @param string $alias
     *
     * @return ResultInterface
     */
    public function avg(string $column/*, string $alias = 'avg'*/): float
    {
        return $this->queryBuild->avg($column);
    }

    /**
     * @param string $column
     * @param string $alias
     *
     * @return ResultInterface
     */
    public function sum(string $column/*, string $alias = 'sum'*/): float
    {
        return $this->queryBuild->sum($column);
    }

    /**
     * 排他锁语句
     */
    public function forUpdate(): self
    {
        $this->queryBuild->lockForUpdate();

        return $this;
    }
    /**
     * 设置多个参数
     *
     * @param array $parameters
     *    $parameters = [
     *    'key1' => 'value1',
     *    'key2' => 'value2',
     *    ]
     *    $parameters = [
     *    'value1',
     *    'value12',
     *    ]
     *    $parameters = [
     *    ['key', 'value', 'type'],
     *    ['key', 'value'],
     *    ['key', 'value', 'type'],
     *    ]
     * @throws \Swoft\Db\Exception\DbException
     * @return $this
     */
    public function bind(array $condition): self
    {
        $this->queryBuild->setParameters($condition);

        return $this;
    }

    /**
     * Increment a column's value by a given amount.
     *
     * @param string    $column
     * @param float|int $amount
     * @param array     $extra
     *
     * @return int
     * @throws ContainerException
     * @throws DbException
     * @throws ReflectionException
     */
    public function increment(string $column, $amount = 1, array $extra = []): int
    {
    	return $this->queryBuild->increment($column, $amount, $extra);
    }
    
    /**
     * Decrement a column's value by a given amount.
     *
     * @param string    $column
     * @param float|int $amount
     * @param array     $extra
     *
     * @return int
     * @throws ContainerException
     * @throws DbException
     * @throws ReflectionException
     */
    public function decrement(string $column, $amount = 1, array $extra = []): int
    {
    	return $this->queryBuild->decrement($column, $amount, $extra);
    }
    
    public function lockForUpdate(): self
    {
    	 $this->queryBuild->lockForUpdate();
    	 
    	 return $this;
    }
    
    /**
     * @param string $column
     * @return int
     */
    public function count(string $column = '*'/*, string $alias = 'count'*/): int
    {
        return (int)$this->queryBuild->count($column);
    }

    /**
     * 析构方法
     */
    public function __destruct()
    {
        $this->queryBuild = null;
    }
}